use crate::{models::NL, pgsql::Constraint};
use regex::Regex;
use sqlx::{
    postgres::{types::Oid as PgOid, PgRow},
    Row,
};

pub struct Table {
    pub oid: PgOid,
    schema: String,
    name: String,
    name_purge: String,
    spc_name: Option<String>,
    columns: Vec<TableColumn>,
    constraints: Vec<Constraint>,
    indexes: Vec<TableIndex>,
    //primary_keys: Option<Vec<i16>>,
    //primary_key_name: Option<String>, // constraints: Vec<Constraint>,
}

impl Table {
    pub fn new(oid: PgOid, schema: String, name: &str, spc_name: Option<String>) -> Table {
        Table {
            oid,
            schema: format!("\"{schema}\""),
            name: format!("\"{}\"", name.to_string()),
            name_purge: name.to_string(),
            spc_name,
            columns: Vec::<TableColumn>::new(),
            constraints: Vec::<Constraint>::new(),
            indexes: Vec::<TableIndex>::new(),
            // primary_keys: None,
            // primary_key_name: None,
        }
    }

    pub fn to_create_sql(&self) -> String {
        let mut result = String::from(NL);
        result.push_str(&std::fmt::format(format_args!(
            "{}--TABLE {}.{}",
            NL, &self.schema, &self.name
        )));
        // result.push_str(&std::fmt::format(format_args!(
        //     "{}DROP TABLE IF EXISTS {}.{};",
        //     NL, &self.schema, &self.name
        // )));
        result.push_str(&std::fmt::format(format_args!(
            "{}CREATE TABLE IF NOT EXISTS {}.{}(",
            NL, &self.schema, &self.name
        )));
        let mut index = 0;
        for column in &self.columns {
            if index > 0 {
                result.push(',');
            }
            result.push_str(&format!("{NL}\t{}", column.to_sql()));
            index = index + 1;
        }
        // //创建主键
        // match self.get_primary_keys() {
        //     Some(ve) => {
        //         result.push_str(&format!(
        //             ",{NL}\tCONSTRAINT \"{}\" PRIMARY KEY ({})",
        //             self.primary_key_name.as_ref().unwrap(),
        //             ve.into_iter()
        //                 .map(|x| x.as_str())
        //                 .collect::<Vec<&str>>()
        //                 .join(",")
        //         ));
        //     }
        //     _ => (),
        // };
        result.push_str(&format!("{NL})"));
        if let Some(spcname) = self.spc_name.as_ref() {
            result.push_str(&format!("{NL}TABLESPACE \"{}\"", spcname));
        }
        result.push_str(";");
        //添加约束
        for constraint in &self.constraints {
            let str_columns = self.get_column_names(&constraint.keys);
            result.push_str(&format!(
                "{NL}ALTER TABLE {}.{} {} ({});",
                &self.schema,
                &self.name,
                constraint.to_sql(),
                str_columns
            ));
        }
        //添加索引
        for index in &self.indexes {
            result.push_str(&format!("{NL}{};", index.indexdef));
        }
        result
    }

    pub fn name(&self) -> &str {
        &self.name_purge
    }

    pub fn push_column(&mut self, column: TableColumn) {
        let _ = &self.columns.push(column);
    }

    pub fn push_constraint(&mut self, constraint: Constraint) {
        let _ = &self.constraints.push(constraint);
    }

    pub fn push_index(&mut self, index: TableIndex) {
        let _ = &self.indexes.push(index);
    }

    // pub fn set_primary_keys(&mut self, key_name: String, keys: Vec<i16>) {
    //     self.primary_key_name = Some(key_name);
    //     self.primary_keys = Some(keys);
    // }

    pub fn to_drop_sql(&self) -> String {
        format!("{NL}DROP TABLE IF EXISTS {}.{};", self.schema, self.name)
    }

    pub fn to_alter_sql(&self, table: &Table) -> String {
        if self.name != table.name {
            return String::new();
        }
        let mut result = String::new();
        for column1 in &self.columns {
            if let Some(column2) = table.get_column(&column1.name) {
                if let Some(x) = column1.get_alter_name_sql(column2) {
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} {}",
                        &self.schema,
                        &self.name,
                        x.as_str()
                    ));
                }
                if let Some(x) = column1.get_alter_type_sql(column2) {
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} {}",
                        &self.schema,
                        &self.name,
                        x.as_str()
                    ));
                }
                if let Some(x) = column1.get_alter_default_sql(column2) {
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} {}",
                        &self.schema,
                        &self.name,
                        x.as_str()
                    ));
                }
                if let Some(x) = column1.get_alter_null_sql(column2) {
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} {}",
                        &self.schema,
                        &self.name,
                        x.as_str()
                    ));
                }
            } else {
                result.push_str(&format!(
                    "{NL}ALTER TABLE {}.{} ADD COLUMN {};",
                    self.schema,
                    self.name,
                    column1.to_sql()
                ));
            }
        }
        for column2 in &table.columns {
            if let None = self.get_column(&column2.name) {
                result.push_str(&format!(
                    "{NL}ALTER TABLE {}.{} DROP COLUMN {};",
                    self.schema, self.name, &column2.name
                ));
            }
        }
        for constraint1 in &self.constraints {
            if let Some(constraint2) = table.get_constraint(&constraint1.name) {
                if constraint1.is_not_equal(constraint2) {
                    let str_columns = self.get_column_names(&constraint1.keys);
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} DROP CONSTRAINT \"{}\";",
                        &self.schema, &self.name, &constraint1.name
                    ));
                    result.push_str(&format!(
                        "{NL}ALTER TABLE {}.{} {} ({});",
                        &self.schema,
                        &self.name,
                        constraint1.to_sql(),
                        str_columns
                    ));
                }
            } else {
                let str_columns = self.get_column_names(&constraint1.keys);
                result.push_str(&format!(
                    "{NL}ALTER TABLE {}.{} {} ({});",
                    &self.schema,
                    &self.name,
                    constraint1.to_sql(),
                    str_columns
                ));
            }
        }
        for constraint2 in &table.constraints {
            if self.get_constraint(&constraint2.name).is_none() {
                result.push_str(&format!(
                    "{NL}ALTER TABLE {}.{} DROP CONSTRAINT \"{}\";",
                    self.schema, self.name, &constraint2.name
                ));
            }
        }
        for index1 in &self.indexes {
            if let Some(index2) = table.get_index(&index1.name) {
                if index1.is_not_equal(index2) {
                    result.push_str(&format!("{NL}DROP INDEX {};", &index1.name));
                    result.push_str(&index1.indexdef);
                }
            } else {
                result.push_str(&index1.indexdef);
            }
        }
        for index2 in &table.indexes {
            if self.get_index(&index2.name).is_none() {
                result.push_str(&format!("{NL}DROP INDEX {};", &index2.name));
            }
        }
        if result.len() > 0 {
            result.insert_str(
                0,
                &format!("{NL}{NL}--TABLE {}.{}", &self.schema, &self.name),
            );
        }
        result
    }

    fn get_column(&self, name: &String) -> Option<&TableColumn> {
        self.columns.iter().find(|x| x.name == *name)
    }

    #[allow(dead_code)]
    fn get_column_by_order(&self, order: i32) -> Option<&TableColumn> {
        self.columns.iter().find(|x| x.order == order)
    }

    fn get_constraint(&self, name: &String) -> Option<&Constraint> {
        self.constraints.iter().find(|x| &x.name == name)
    }

    fn get_index(&self, name: &String) -> Option<&TableIndex> {
        self.indexes.iter().find(|x| x.name == *name)
    }

    fn get_column_names(&self, keys: &Option<Vec<i16>>) -> String {
        if keys.is_none() {
            return String::new();
        }
        let keys = keys.as_ref().unwrap();
        self.columns
            .iter()
            .filter(|x| keys.contains(&(x.order as i16)))
            .map(|x| x.name.as_str())
            .collect::<Vec<&str>>()
            .join(" , ")
    }

    //CONSTRAINT "REDIS_MONIT_SETTINGS_pkey" PRIMARY KEY ("ID","1")
    // fn get_primary_keys(&self) -> Option<Vec<&String>> {
    // &self.primary_keys.map(|y| {
    //     let result = (&self.columns)
    //         .into_iter()
    //         .filter(|x| y.contains(&x.order))
    //         .map(|x| &x.name)
    //         .collect::<Vec<&String>>();
    // })
    // match &self.primary_keys {
    //     Some(va) => {
    //         // let mut result = Vec::<&String>::new();
    //         let result = (&self.columns)
    //             .into_iter()
    //             .filter(|x| va.contains(&(x.order as i16)))
    //             .map(|x| &x.name)
    //             .collect::<Vec<&String>>();
    //         Some(result)
    //     }
    //     None => None,
    // }
    // }
}

// pub struct Constraint {
//     name: String,
//     keys: Vec<i16>,
// }

#[derive(Debug)]
pub struct TableColumn {
    name: String,
    data_type: String,
    nullable: bool,
    length: Option<i32>,
    default: Option<String>,
    order: i32,
    numeric_precision: Option<i32>,
    numeric_scale: Option<i32>,
}

impl TableColumn {
    pub fn from_row(row: PgRow) -> TableColumn {
        TableColumn {
            name: format!("\"{}\"", row.get::<&str, &str>("column_name")),
            data_type: row.get("udt_name"),
            nullable: row.get::<&str, &str>("is_nullable") == "YES",
            length: row.get("character_maximum_length"),
            default: row.get("column_default"),
            order: row.get("ordinal_position"),
            numeric_precision: row.get("numeric_precision"),
            numeric_scale: row.get("numeric_scale"),
        }
    }

    pub fn to_sql(&self) -> String {
        let mut result = format!("{} ", &self.name);
        let mut is_serial = false;
        // let default_ref = self.default;
        if let Some(x) = &self.default {
            if Regex::new("^nextval\\('[\\s\\S]*'::regclass\\)$")
                .unwrap()
                .is_match(x)
            {
                if (&self.data_type) == "int2" {
                    result.push_str("smallserial");
                } else if (&self.data_type) == "int4" {
                    result.push_str("serial");
                    is_serial = true;
                } else if (&self.data_type) == "int8" {
                    result.push_str("bigserial");
                    is_serial = true;
                }
            }
        }
        if !is_serial {
            if &self.data_type == "numeric" && (&self.numeric_precision).is_some() {
                result.push_str(&format!(
                    "{}({},{})",
                    &self.data_type,
                    &self.numeric_precision.unwrap(),
                    &self.numeric_scale.unwrap()
                ));
            } else if (&self.data_type).starts_with("_") {
                result.push_str(&format!("{}[]", &self.data_type[1..]));
            } else {
                result.push_str(&self.data_type);
            }
            match self.length {
                Some(len) => {
                    result.push_str(&format!("({})", len.to_string()));
                }
                _ => (),
            };
        }

        if self.nullable == false {
            result.push_str(" NOT NULL");
        }
        if !is_serial {
            if let Some(x) = &self.default {
                result.push_str(&format!(" DEFAULT {}", x));
            }
        }
        // result.push_str(",");
        result
    }

    // pub fn is_equal(&self, column: &TableColumn) -> bool {
    //     self.data_type == column.data_type
    //         && self.nullable == column.nullable
    //         && self.length == column.length
    //         && self.default == column.default
    // }

    ///alter table "TEST" rename column "CreateTime" to "CreateTime1";
    pub fn get_alter_name_sql(&self, column: &TableColumn) -> Option<String> {
        if self.name != column.name {
            Some(format!("RENAME COLUMN {} TO {};", &self.name, &column.name))
        } else {
            None
        }
    }

    pub fn get_alter_type_sql(&self, column: &TableColumn) -> Option<String> {
        if self.data_type != column.data_type || self.length != column.length {
            let mut content = format!(
                "ALTER {} TYPE {} USING {}::{}",
                &self.name, column.data_type, &self.name, &self.data_type
            );
            if let Some(x) = self.length {
                content.push_str(&format!("({})", x));
            }
            content.push(';');
            Some(content)
        } else {
            None
        }
    }

    pub fn get_alter_default_sql(&self, column: &TableColumn) -> Option<String> {
        if self.default == column.default {
            None
        } else if self.default.is_none() && column.default.is_some() {
            Some(format!("ALTER COLUMN {} DROP DEFAULT;", &self.name))
        } else if self.default.is_some() && column.default.is_none() {
            Some(format!(
                "ALTER COLUMN {} SET DEFAULT {};",
                &self.name,
                self.default.as_ref().unwrap()
            ))
        } else if let (Some(x1), Some(x2)) = (&self.default, &column.default) {
            if x1.starts_with("nextval(")
                && x1.ends_with("::regclass)")
                && x2.starts_with("nextval(")
                && x2.ends_with("::regclass)")
            {
                return None;
            }
            Some(format!("ALTER COLUMN {} SET DEFAULT {};", &self.name, x1))
        } else {
            None
        }
    }

    pub fn get_alter_null_sql(&self, column: &TableColumn) -> Option<String> {
        if self.nullable && column.nullable == false {
            Some(format!("ALTER COLUMN {} DROP NOT NUL;", &self.name))
        } else if self.nullable == false && column.nullable {
            Some(format!("ALTER COLUMN {} SET NOT NULL;", &self.name))
        } else {
            None
        }
    }

    // pub fn is_not_equal(&self, column: &TableColumn) -> bool {
    //     self.data_type != column.data_type
    //         || self.nullable != column.nullable
    //         || self.length != column.length
    //         || ({
    //             if let (Some(x1), Some(x2)) = (&self.default, &column.default) {
    //                 if x1.starts_with("nextval(")
    //                     && x1.ends_with("::regclass)")
    //                     && x2.starts_with("nextval(")
    //                     && x2.ends_with("::regclass)")
    //                 {
    //                     return false;
    //                 }
    //             }
    //             self.default != column.default
    //         })
    // }
}

pub struct TableIndex {
    name: String,
    indexdef: String,
}

impl TableIndex {
    pub fn from(row: &PgRow) -> Self {
        TableIndex {
            name: format!("\"{}\"", row.get::<&str, &str>("indexname")),
            indexdef: row.get("indexdef"),
        }
    }

    fn is_not_equal(&self, constraint: &TableIndex) -> bool {
        self.indexdef != constraint.indexdef
    }
}
